package com.razorthink.shell.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.razorthink.shell.core.entities.User;

public class DerbyDB {

	String dbName = "testdb";
	String connectionURL;
	String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	Statement stmt;
	Connection conn;
	User user;
	private static DerbyDB instance;

	public DerbyDB() throws SQLException {
		connectionURL = "jdbc:derby:" + dbName + ";create=true";
		conn = DriverManager.getConnection(connectionURL);
		stmt = conn.createStatement();
		user = new User();
		init();
	}

	public static DerbyDB getInstance() throws SQLException {
		if (instance == null) {
			instance = new DerbyDB();
		}
		return instance;
	}

	private void init() throws SQLException {
		createDb("shelldb");
		User u = getUser("admin");
		if (u == null) {
			insertUser("admin", "Administrator", "admin",
					System.currentTimeMillis());
		}
	}

	public boolean createDb(String dbName) throws SQLException {
		boolean status = checkForDbExististence(conn);
		String createString = "CREATE TABLE USERS  "
				+ "(USERNAME VARCHAR(100) NOT NULL ,NAME VARCHAR(100) NOT NULL"
				+ ",PASSWORD VARCHAR(100) NOT NULL,LASTLOGIN BIGINT NOT NULL,UNIQUE (NAME)) ";
		if (!status) {
			stmt.execute(createString);
			// System.out.println("table created");
		} else {
			// System.out.println("table already created");
		}
		return true;
	}

	public boolean insertUser(String userName, String name, String passWord,
			long lastLogin) throws SQLException {
		String query = "INSERT INTO users VALUES ('" + userName + "','" + name
				+ "','" + passWord + "'," + lastLogin + ")";
		int count = stmt.executeUpdate(query);
		if (count > 0) {
			// System.out.println("Inserted.");
			return true;
		}
		return false;

	}

	public boolean editUser(String userName, String name, String passWord,
			long lastLogin) throws SQLException {
		String query = "UPDATE users SET " + " NAME='" + name + "', PASSWORD='"
				+ passWord + "', LASTLOGIN=" + lastLogin + " WHERE "
				+ "USERNAME='" + userName + "'";
		int count = stmt.executeUpdate(query);
		if (count > 0) {
			// System.out.println("User Edited.");
			return true;
		}
		return false;
	}

	public boolean updateLogin(String userName, long lastLogin)
			throws SQLException {
		String query = "UPDATE users SET " + "LASTLOGIN=" + lastLogin
				+ " WHERE " + "USERNAME='" + userName + "'";
		int count = stmt.executeUpdate(query);
		if (count > 0) {
			// System.out.println("Last Login Updated");
			return true;
		}
		return false;
	}

	public boolean deleteUser(String userName) throws SQLException {
		String query = "DELETE FROM users WHERE USERNAME='" + userName + "'";
		int count = stmt.executeUpdate(query);
		if (count > 0) {
			// System.out.println("User Deleted.");
			return true;
		}
		return false;
	}

	public User getUser(String userName) throws SQLException {
		String query = "select * from users WHERE USERNAME='" + userName + "'";
		ResultSet res = null;
		res = stmt.executeQuery(query);
		if (res.next()) {
			user.setUserName(res.getString(1));
			user.setName(res.getString(2));
			user.setPassword(res.getString(3));
			user.setLastLogin(res.getLong(4));
			return user;
		}
		return null;
	}

	public User validateUser(String userName, String password)
			throws SQLException {
		String query = "select * from users WHERE USERNAME='" + userName
				+ "' and PASSWORD='" + password + "'";
		ResultSet res = null;
		res = stmt.executeQuery(query);
		if (res.next()) {
			user.setUserName(res.getString(1));
			user.setName(res.getString(2));
			user.setPassword(res.getString(3));
			user.setLastLogin(res.getLong(4));
			return user;
		}
		return null;
	}

	public List<User> getAllUsers() throws SQLException {
		String query = "select * from users";
		List<User> list = new ArrayList<>();
		ResultSet res = null;
		res = stmt.executeQuery(query);
		while (res.next()) {
			user.setUserName(res.getString(1));
			user.setName(res.getString(2));
			user.setPassword(res.getString(3));
			user.setLastLogin(res.getLong(4));
			list.add(user);
		}
		return list;
	}

	public boolean checkForDbExististence(Connection conn) throws SQLException {
		try {
			Statement s = conn.createStatement();
			s.executeQuery("SELECT  * FROM users");
		} catch (SQLException sqle) {
			String theError = sqle.getSQLState();
			/** If table exists will get - WARNING 02000: No row was found **/
			if (theError.equals("42X05")) // Table does not exist
			{
				return false;
			} else {
				throw sqle;
			}
		}
		return true;
	}
}
